﻿using BLL;
using Mono.Data.Sqlite;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

// 员工管理数据库服务层
namespace DAL
{
    public class PersonDao
    {
        public List<Person> list(Person employee)
        {
            // 定义SQL
            string sql = "select * from person where 1=1";
            if (employee.PersonName != null && employee.PersonName != "")
            {
                sql += " and PersonName like @PersonName ";
            }
            if (employee.IdNum != null && employee.IdNum != "")
            {
                sql += " and  IdNum = @IdNum ";
            }
            if (employee.DepartmentName != null && employee.DepartmentName != "")
            {
                sql += " and DepartmentName = @DepartmentName";
            } if (employee.IsDel != null && employee.IsDel != "")
            {
                sql += " and IsDel = @IsDel";
            }
            SqliteParameter[] sp = {
                new SqliteParameter("PersonName", "%" + employee.PersonName + "%"),
                new SqliteParameter("IdNum", employee.IdNum),
                new SqliteParameter("DepartmentName", employee.DepartmentName),
                new SqliteParameter("IsDel", employee.IsDel)
            };
            // 执行SQL
            List<List<Dictionary<string, object>>> data = SQLiteHelper.ExecuteMap(sql, sp);
            // 数据库 转 MODEL对象
            List<Person> list = SQLiteHelper.DataToModel<Person>(data);
            return list;
        }

        public int updateById(Person person)
        {
            // 定义SQL
            string sql = "update person set" +
                " PersonName = @PersonName," +
                " Gender = @Gender, " +
                " Age = @Age," +
                " Address = @Address," +
                " PhoneNum = @PhoneNum," +
                " DepartmentName = @DepartmentName," +
                " IdNum = @IdNum" +
                " where PersonID = @PersonID";

            SqliteParameter[] sp = {
                new SqliteParameter("PersonID", person.PersonID),
                new SqliteParameter("PersonName", person.PersonName),
                new SqliteParameter("Gender", person.Gender),
                new SqliteParameter("Age", person.Age),
                new SqliteParameter("Address", person.Address),
                new SqliteParameter("PhoneNum", person.PhoneNum),
                new SqliteParameter("DepartmentName", person.DepartmentName),
                new SqliteParameter("IsDel", person.IsDel),
                new SqliteParameter("IdNum", person.IdNum),
            };
            // 执行SQL
            return SQLiteHelper.ExecuteNonQuery(sql, sp);

        }

        public int deleteById(string id)
        {
            // 定义SQL
            string sql = "delete from person  where PersonID = @PersonID";
            SqliteParameter[] sp = {
            new SqliteParameter("PersonID",id)
            };
            return SQLiteHelper.ExecuteNonQuery(sql, sp);
        }

        public string getMaxId()
        {
            // 定义SQL
            string sql = "select max(PersonID) as PersonID from person";
            SqliteParameter[] sp = new SqliteParameter[0];
            // 执行SQL
            List<List<Dictionary<string, object>>> data = SQLiteHelper.ExecuteMap(sql, sp);
            // 数据库 转 MODEL对象
            List<Person> list = SQLiteHelper.DataToModel<Person>(data);
            return list[0].PersonID;
        }

        public int save(Person person)
        {
            // 定义SQL
            string sql = "insert into person values(@PersonID,@PersonName,@Gender,@Age,@Address,@PhoneNum,@DepartmentName,@IsDel,@IdNum)";
            SqliteParameter[] sp = {
                new SqliteParameter("PersonID", person.PersonID),
                new SqliteParameter("PersonName", person.PersonName),
                new SqliteParameter("Gender", person.Gender),
                new SqliteParameter("Age", person.Age),
                new SqliteParameter("Address", person.Address),
                new SqliteParameter("PhoneNum", person.PhoneNum),
                new SqliteParameter("DepartmentName", person.DepartmentName),
                new SqliteParameter("IsDel", person.IsDel),
                new SqliteParameter("IdNum", person.IdNum),
            };
            // 执行SQL
            return SQLiteHelper.ExecuteNonQuery(sql, sp);
        }
    }
}
